﻿using System;
using System.Data;
using System.Text;
using System.Data.SQLite;
using Clipper.Model;

namespace Clipper.DAL
{
	/// <summary>
	/// 数据访问类:SampleInfo
	/// </summary>
	public partial class SampleInfo
	{
        SampleType mSampleTypeDal = new SampleType();
		public SampleInfo()
		{}
		#region  BasicMethod

		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(long sampleID)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from SampleInfo");
			strSql.Append(" where sampleID=@sampleID ");
			SQLiteParameter[] parameters = {
					new SQLiteParameter("@sampleID", DbType.Int64,8)			};
			parameters[0].Value = sampleID;

			return SQLiteHelper.Exists(strSql.ToString(),parameters);
		}


		/// <summary>
		/// 增加一条数据
		/// </summary>
		public bool Add(Clipper.Model.SampleInfo model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into SampleInfo(");
			strSql.Append("sampleID,patientID,sampleType,carryDoctor,carryDate,trier,assessor,diagnose,describe,SampleState,smpleBarcode,createTime,note)");
			strSql.Append(" values (");
			strSql.Append("@sampleID,@patientID,@sampleType,@carryDoctor,@carryDate,@trier,@assessor,@diagnose,@describe,@SampleState,@smpleBarcode,@createTime,@note)");
			SQLiteParameter[] parameters = {
					new SQLiteParameter("@sampleID", DbType.Int64,8),
					new SQLiteParameter("@patientID", DbType.Int64,8),
					new SQLiteParameter("@sampleType", DbType.Int32,4),
					new SQLiteParameter("@carryDoctor", DbType.Int32,4),
					new SQLiteParameter("@carryDate", DbType.DateTime),
					new SQLiteParameter("@trier", DbType.String,20),
					new SQLiteParameter("@assessor", DbType.String,20),
					new SQLiteParameter("@diagnose", DbType.String,100),
					new SQLiteParameter("@describe", DbType.String),
					new SQLiteParameter("@SampleState", DbType.Int32,4),
					new SQLiteParameter("@smpleBarcode", DbType.String,30),
					new SQLiteParameter("@createTime", DbType.DateTime),
					new SQLiteParameter("@note", DbType.String)};
			parameters[0].Value = model.sampleID;
			parameters[1].Value = model.patientID;
			parameters[2].Value = model.sampleType;
			parameters[3].Value = model.carryDoctor;
			parameters[4].Value = model.carryDate;
			parameters[5].Value = model.trier;
			parameters[6].Value = model.assessor;
			parameters[7].Value = model.diagnose;
			parameters[8].Value = model.describe;
			parameters[9].Value = model.SampleState;
			parameters[10].Value = model.sampleBarcode;
			parameters[11].Value = model.createTime;
			parameters[12].Value = model.note;

			int rows=SQLiteHelper.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(Clipper.Model.SampleInfo model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update SampleInfo set ");
			strSql.Append("patientID=@patientID,");
			strSql.Append("sampleType=@sampleType,");
			strSql.Append("carryDoctor=@carryDoctor,");
			strSql.Append("carryDate=@carryDate,");
			strSql.Append("trier=@trier,");
			strSql.Append("assessor=@assessor,");
			strSql.Append("diagnose=@diagnose,");
			strSql.Append("describe=@describe,");
			strSql.Append("SampleState=@SampleState,");
			strSql.Append("smpleBarcode=@smpleBarcode,");
			strSql.Append("createTime=@createTime,");
			strSql.Append("note=@note");
			strSql.Append(" where sampleID=@sampleID ");
			SQLiteParameter[] parameters = {
					new SQLiteParameter("@patientID", DbType.Int64,8),
					new SQLiteParameter("@sampleType", DbType.Int32,4),
					new SQLiteParameter("@carryDoctor", DbType.Int32,4),
					new SQLiteParameter("@carryDate", DbType.DateTime),
					new SQLiteParameter("@trier", DbType.String,20),
					new SQLiteParameter("@assessor", DbType.String,20),
					new SQLiteParameter("@diagnose", DbType.String,100),
					new SQLiteParameter("@describe", DbType.String),
					new SQLiteParameter("@SampleState", DbType.Int32,4),
					new SQLiteParameter("@smpleBarcode", DbType.String,30),
					new SQLiteParameter("@createTime", DbType.DateTime),
					new SQLiteParameter("@note", DbType.String),
					new SQLiteParameter("@sampleID", DbType.Int64,8)};
			parameters[0].Value = model.patientID;
			parameters[1].Value = model.sampleType;
			parameters[2].Value = model.carryDoctor;
			parameters[3].Value = model.carryDate;
			parameters[4].Value = model.trier;
			parameters[5].Value = model.assessor;
			parameters[6].Value = model.diagnose;
			parameters[7].Value = model.describe;
			parameters[8].Value = model.SampleState;
			parameters[9].Value = model.sampleBarcode;
			parameters[10].Value = model.createTime;
			parameters[11].Value = model.note;
			parameters[12].Value = model.sampleID;

			int rows=SQLiteHelper.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(long sampleID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from SampleInfo ");
			strSql.Append(" where sampleID=@sampleID ");
			SQLiteParameter[] parameters = {
					new SQLiteParameter("@sampleID", DbType.Int64,8)			};
			parameters[0].Value = sampleID;

			int rows=SQLiteHelper.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string sampleIDlist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from SampleInfo ");
			strSql.Append(" where sampleID in ("+sampleIDlist + ")  ");
			int rows=SQLiteHelper.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public Clipper.Model.SampleInfo GetModel(long sampleID)
		{

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select sampleID,patientID,sampleType,carryDoctor,carryDate,trier,assessor,diagnose,describe,SampleState,smpleBarcode,createTime,note from SampleInfo ");
            strSql.Append(" where sampleID=@sampleID ");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@sampleID", DbType.Int64,8)			};
            parameters[0].Value = sampleID;

			Clipper.Model.SampleInfo model=new Clipper.Model.SampleInfo();
			DataSet ds=SQLiteHelper.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				return DataRowToModel(ds.Tables[0].Rows[0]);
			}
			else
			{
				return null;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public Clipper.Model.SampleInfo DataRowToModel(DataRow row)
		{
			Clipper.Model.SampleInfo model=new Clipper.Model.SampleInfo();
            if (row != null)
            {
                if (row["sampleID"] != null && row["sampleID"].ToString() != "")
                {
                    model.sampleID = long.Parse(row["sampleID"].ToString());
                }
                if (row["patientID"] != null && row["patientID"].ToString() != "")
                {
                    model.patientID = long.Parse(row["patientID"].ToString());

                }
                if (row["sampleType"] != null && row["sampleType"].ToString() != "")
                {
                    model.sampleType = byte.Parse(row["sampleType"].ToString());
                    model.SampleTypeName = mSampleTypeDal.GetModel(model.sampleType).typeName;
                }
                if (row["carryDoctor"] != null && row["carryDoctor"].ToString() != "")
                {
                    model.carryDoctor = int.Parse(row["carryDoctor"].ToString());
                }
                if (row["carryDate"] != null && row["carryDate"].ToString() != "")
                {
                    model.carryDate = DateTime.Parse(row["carryDate"].ToString());
                }
                if (row["trier"] != null)
                {
                    model.trier = row["trier"].ToString();
                }
                if (row["assessor"] != null)
                {
                    model.assessor = row["assessor"].ToString();
                }
                if (row["diagnose"] != null)
                {
                    model.diagnose = row["diagnose"].ToString();
                }
                if (row["describe"] != null)
                {
                    model.describe = row["describe"].ToString();
                }
                if (row["SampleState"] != null && row["SampleState"].ToString() != "")
                {
                    model.SampleState = int.Parse(row["SampleState"].ToString());
                }
                if (row["smpleBarcode"] != null)
                {
                    model.sampleBarcode = row["smpleBarcode"].ToString();
                }
                if (row["createTime"] != null && row["createTime"].ToString() != "")
                {
                    model.createTime = DateTime.Parse(row["createTime"].ToString());
                }
                if (row["note"] != null)
                {
                    model.note = row["note"].ToString();
                }
            }
			return model;
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select sampleID,patientID,sampleType,carryDoctor,carryDate,trier,assessor,diagnose,describe,SampleState,smpleBarcode,createTime,note ");
            strSql.Append(" FROM SampleInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
			return SQLiteHelper.Query(strSql.ToString());
		}
        /// <summary>
        /// 查询指定时间内的
        /// </summary>
        /// <param name="from"></param>
        /// <param name="to"></param>
        /// <returns></returns>
        public DataSet GetList(string from, string to)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select sampleID,patientID,sampleType,carryDoctor,carryDate,trier,assessor,diagnose,describe,SampleState,smpleBarcode,createTime,note ");
            strSql.Append(" FROM SampleInfo where createTime>=@from and createTime<=@to");
            SQLiteParameter[] parameters = {
					new SQLiteParameter("@from", DbType.DateTime),
                    new SQLiteParameter("@to",DbType.DateTime),
                                           };
            parameters[0].Value = from;
            parameters[1].Value = to;

            return SQLiteHelper.Query(strSql.ToString(),parameters);
        }
		/// <summary>
		/// 获取记录总数
		/// </summary>
		public int GetRecordCount(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) FROM SampleInfo ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			object obj = SQLiteHelper.GetSingle(strSql.ToString());
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby );
			}
			else
			{
				strSql.Append("order by T.sampleID desc");
			}
			strSql.Append(")AS Row, T.*  from SampleInfo T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return SQLiteHelper.Query(strSql.ToString());
		}

		/*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			SQLiteParameter[] parameters = {
					new SQLiteParameter("@tblName", DbType.VarChar, 255),
					new SQLiteParameter("@fldName", DbType.VarChar, 255),
					new SQLiteParameter("@PageSize", DbType.Int32),
					new SQLiteParameter("@PageIndex", DbType.Int32),
					new SQLiteParameter("@IsReCount", DbType.bit),
					new SQLiteParameter("@OrderType", DbType.bit),
					new SQLiteParameter("@strWhere", DbType.VarChar,1000),
					};
			parameters[0].Value = "SampleInfo";
			parameters[1].Value = "sampleID";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return SQLiteHelper.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

		#endregion  BasicMethod
		#region  ExtensionMethod

		#endregion  ExtensionMethod
	}
}

